package com.jiepos.mpos.biz.operationlog.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;
import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.jiepos.mpos.common.constant.DBConstant.Table;
import com.jiepos.mpos.common.dao.LogFourElementsDao;
import com.jiepos.mpos.common.entity.App_Log_Four_ElementsEntity;
import com.jiepos.mpos.core.entity.Pagination;
import com.jiepos.mpos.core.service.BaseService;
import com.jiepos.mpos.core.util.ExcelUtil;
import com.jiepos.mpos.core.util.StringUtils;

@Service
public class FourEleFlowService extends BaseService {
	
	/** 导出excel文件表头*/
	private static List<String> excelHead = null;
	/** sheet页名称*/
	private static String SHEET_NAME = "四要素验证流水";
	
	@Autowired
	private LogFourElementsDao logFourElementsDao;
	
	/**
	 * 获取四要素认证流水Json
	 * @param pagination
	 * @param request
	 * @param app_Log_Four_ElementsEntity
	 * @return
	 */
	public Map<String, Object> getfourEleFlowLogList(Pagination pagination,HttpServletRequest request,App_Log_Four_ElementsEntity app_Log_Four_ElementsEntity){
		pagination.setQueryString(getQuerySql(app_Log_Four_ElementsEntity));
		pagination = this.getOrderSql(getInsAuthSql(request, pagination, "t1", null), "t1");
		return logFourElementsDao.getPaginationMap(pagination, "t1", getQueryParams(app_Log_Four_ElementsEntity));
	}
	
	/**
	 * 获取导出列表数据
	 * @param request
	 * @param app_Log_Four_ElementsEntity
	 * @return
	 */
	public String exportList(HttpServletRequest request, String fileName,App_Log_Four_ElementsEntity app_Log_Four_ElementsEntity){
		String sql = getOrderSql(getInsAuthSql(request, getQuerySql(app_Log_Four_ElementsEntity), "t1", null), "t1");
		List<Map> dataList = logFourElementsDao.queryForList(sql, getQueryParams(app_Log_Four_ElementsEntity));
		return ExcelUtil.writeExcel(fileName, SHEET_NAME, excelHead, dataList);
	}
	
	/**
	 * 组装查询sql
	 * @param app_Log_Four_ElementsEntity
	 * @return
	 */
	private String getQuerySql(App_Log_Four_ElementsEntity app_Log_Four_ElementsEntity){
		StringBuffer sb = new StringBuffer();
		sb.append("SELECT t1.FOURELEMENT_UUID,")
			.append(" TO_CHAR(TO_DATE (LOC_DT || LOC_TS,'yyyy-MM-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') AS LOC_TS,")
			.append(" t1.LOC_TRACE_NO,")
			.append(" t1.NAME,decode(t1.PHONE,null,'',substr(t1.PHONE,1,3) || '****' || substr(t1.PHONE,length(t1.PHONE)-3,4)) as PHONE,")
			.append(" CONCAT (RPAD (SUBSTR (t1.CERTIFICATE_NO, 1, 6),LENGTH (t1.CERTIFICATE_NO) - 4,'*'),SUBSTR (t1.CERTIFICATE_NO,LENGTH (t1.CERTIFICATE_NO) - 3,4)) AS \"CERTIFICATE_NO\",")
			.append(" CONCAT (RPAD (SUBSTR (t1.ACCT_NO, 1, 6),LENGTH (t1.ACCT_NO) - 4,'*'),SUBSTR (t1.ACCT_NO,LENGTH (t1.ACCT_NO) - 3,4)) AS \"ACCT_NO\", NVL2(II.INS_CD, II.INS_CD || '-' || II.INS_CN_NM, '') INS_NAME,")
			.append(" t1.RSP_CODE || '-' || t1.RSP_MSG RSP_CODE, ")
			.append(" t1.CHANNEL_TRACE_NO, ")
			.append(" TO_CHAR(TO_DATE(RSP_DT || RSP_TS,'yyyy-MM-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') RSP_TIME from ")
			.append(Table.T_APP_LOG_FOUR_ELEMENTS).append(" t1")
			.append(" LEFT JOIN ").append(Table.T_APP_INS_INF).append(" II")
			.append(" ON II.INS_UUID = t1.INS_UUID")
			.append(" LEFT JOIN ").append(Table.T_APP_CODE_LIST).append(" CL")
			.append(" ON t1.RSP_CODE = CL.CODE_VAL")
			.append(" where 1=1");
		/*（不）收费*/
		if(!StringUtils.isBlank(app_Log_Four_ElementsEntity.getRsp_code())){
			sb.append(" and CL.CODE_TP = ?");
		}
		/*会员姓名*/
		if(!StringUtils.isBlank(app_Log_Four_ElementsEntity.getName())){
			sb.append(" and t1.name like ?");
		}
		/*银行卡号*/
		if(!StringUtils.isBlank(app_Log_Four_ElementsEntity.getAcct_no())){
			sb.append(" and t1.acct_no like ?");
		}
		/*手机号码*/
		if(!StringUtils.isBlank(app_Log_Four_ElementsEntity.getPhone())){
			sb.append(" and t1.phone like ?");
		}
		/*O单机构标识*/
		if(!StringUtils.isBlank(app_Log_Four_ElementsEntity.getIns_uuid())){
			sb.append(" and t1.INS_UUID = ?");
		}
		/*验证结束日期,复用字段*/
		if(!StringUtils.isBlank(app_Log_Four_ElementsEntity.getLoc_ts())){
			sb.append(" and t1.LOC_DT <= ?");
		}
		/*验证起始日期*/
		if(!StringUtils.isBlank(app_Log_Four_ElementsEntity.getLoc_dt())){
			sb.append(" and t1.LOC_DT >= ?");
		}
		return sb.toString();
	}
	
	/**
	 * 组装查询参数
	 * @param app_Log_Four_ElementsEntity
	 * @return
	 */
	private List<String> getQueryParams(App_Log_Four_ElementsEntity app_Log_Four_ElementsEntity){
		List<String> list = new ArrayList<>();
		/*（不）收费*/
		if(!StringUtils.isBlank(app_Log_Four_ElementsEntity.getRsp_code())){
			list.add(app_Log_Four_ElementsEntity.getRsp_code());
		}
		/*会员姓名*/
		if(!StringUtils.isBlank(app_Log_Four_ElementsEntity.getName())){
			list.add("%"+app_Log_Four_ElementsEntity.getName()+"%");
		}
		/*银行卡号*/
		if(!StringUtils.isBlank(app_Log_Four_ElementsEntity.getAcct_no())){
			list.add("%"+app_Log_Four_ElementsEntity.getAcct_no()+"%");
		}
		/*手机号码*/
		if(!StringUtils.isBlank(app_Log_Four_ElementsEntity.getPhone())){
			list.add("%"+app_Log_Four_ElementsEntity.getPhone()+"%");
		}
		/*O单机构标识*/
		if(!StringUtils.isBlank(app_Log_Four_ElementsEntity.getIns_uuid())){
			list.add(app_Log_Four_ElementsEntity.getIns_uuid());
		}
		/*验证结束日期,复用字段*/
		if(!StringUtils.isBlank(app_Log_Four_ElementsEntity.getLoc_ts())){
			list.add(app_Log_Four_ElementsEntity.getLoc_ts().replaceAll("-", ""));
		}
		/*验证起始日期*/
		if(!StringUtils.isBlank(app_Log_Four_ElementsEntity.getLoc_dt())){
			list.add(app_Log_Four_ElementsEntity.getLoc_dt().replaceAll("-", ""));
		}
		return list;
	}
	
	@PostConstruct
	private void init(){
		excelHead = new ArrayList<>();
		excelHead.add("验证时间");
		excelHead.add("本地流水号");
		excelHead.add("客户姓名");
		excelHead.add("手机号码");
		excelHead.add("证件号");
		excelHead.add("银行卡号");
		excelHead.add("所属机构");
		excelHead.add("响应码-消息");
		excelHead.add("渠道流水号");
		excelHead.add("响应时间");
	}
}
